import os
import psycopg2
import pymysql
import time
import schedule
from dotenv import load_dotenv

# 加载环境变量
load_dotenv()

# 本地PostgreSQL数据库连接配置
local_db_config = {
    'host': os.environ.get('LOCAL_DB_HOST', 'localhost'),
    'user': os.environ.get('LOCAL_DB_USER', 'queryview'),
    'password': os.environ.get('LOCAL_DB_PASSWORD', '123456'),
    'database': os.environ.get('LOCAL_DB_NAME', 'bee'),
    'port': int(os.environ.get('LOCAL_DB_PORT', '25437'))
}

# 远程MySQL数据库连接配置
remote_db_config = {
    'host': os.environ.get('DB_HOST', 'localhost'),
    'user': os.environ.get('DB_USER', 'root'),
    'password': os.environ.get('DB_PASSWORD'),
    'database': os.environ.get('DB_NAME', 'basal_adjust')
}


def check_invalid_utf8(data):
    """检查是否包含无效的UTF-8字符"""
    try:
        return data.decode('utf-8')
    except UnicodeDecodeError:
        return None


def connect_to_local_db():
    """连接到本地PostgreSQL数据库"""
    try:
        if not local_db_config['password']:
            print("错误：本地数据库密码未配置。请在.env文件中设置LOCAL_DB_PASSWORD")
            return None
            
        conn = psycopg2.connect(**local_db_config)
        print("成功连接到本地PostgreSQL数据库")
        return conn
    except Exception as e:
        print(f"连接本地数据库失败: {e}")
        return None


def connect_to_remote_db():
    """连接到远程MySQL数据库"""
    try:
        if not remote_db_config['password']:
            print("错误：远程数据库密码未配置。请在.env文件中设置DB_PASSWORD")
            return None
            
        conn = pymysql.connect(**remote_db_config)
        print("成功连接到远程MySQL数据库")
        return conn
    except Exception as e:
        print(f"连接远程数据库失败: {e}")
        return None


def sync_cgm_data():
    """同步CGM数据从本地PostgreSQL到远程MySQL"""
    print("开始同步CGM数据...")
    
    local_conn = connect_to_local_db()
    remote_conn = connect_to_remote_db()
    
    if not local_conn or not remote_conn:
        print("数据库连接失败，同步中止")
        return
    
    try:
        local_cursor = local_conn.cursor()
        remote_cursor = remote_conn.cursor()
        
        # 获取本地数据
        local_cursor.execute("SELECT * FROM cgm_data ORDER BY timestamp DESC LIMIT 1000")
        cgm_data = local_cursor.fetchall()
        
        print(f"从本地数据库获取到 {len(cgm_data)} 条CGM记录")
        
        # 同步到远程数据库
        for record in cgm_data:
            try:
                # 检查记录是否已存在
                check_query = "SELECT COUNT(*) FROM cgm_data WHERE patient_id = %s AND timestamp = %s"
                remote_cursor.execute(check_query, (record[0], record[1]))
                exists = remote_cursor.fetchone()[0]
                
                if not exists:
                    # 插入新记录
                    insert_query = """
                    INSERT INTO cgm_data (patient_id, timestamp, glucose_value, trend_arrow)
                    VALUES (%s, %s, %s, %s)
                    """
                    remote_cursor.execute(insert_query, record)
                    
            except Exception as e:
                print(f"同步记录失败: {e}")
                continue
        
        remote_conn.commit()
        print("CGM数据同步完成")
        
    except Exception as e:
        print(f"数据同步过程中出错: {e}")
    finally:
        if local_conn:
            local_conn.close()
        if remote_conn:
            remote_conn.close()


def schedule_sync():
    """设置定时同步任务"""
    print("设置CGM数据定时同步任务...")
    
    # 每30分钟同步一次
    schedule.every(30).minutes.do(sync_cgm_data)
    
    while True:
        schedule.run_pending()
        time.sleep(60)  # 每分钟检查一次任务


if __name__ == "__main__":
    # 立即执行一次同步
    sync_cgm_data()
    
    # 启动定时任务
    # schedule_sync()
